#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive

if [[ $1 == "" ]];then
   db=zx_dws
else
   db=$1
fi

${HIVE_HOME} -S -e "CREATE DATABASE IF NOT EXISTS ${db};

DROP TABLE dws_visit_consult.dws_visit_consult_table;
CREATE  TABLE IF NOT EXISTS dws_visit_consult.dws_visit_consult_table
(

        year string COMMENT '年份',
        quarter string COMMENT '季度',
        month string COMMENT '月份',
        day string COMMENT '天',
        area string COMMENT '地区',
        origin_channel string COMMENT '来源渠道',
        seo_source string COMMENT '搜索来源',
        from_url string COMMENT '来源页面',

        year_sign string COMMENT '年份标记',
        quarter_sign string COMMENT '季度标记',
        month_sign string COMMENT '月份标记',
        day_sign string COMMENT '天标记',
        area_sign string COMMENT '地区标记',
        origin_channel_sign string COMMENT '来源渠道标记',
        seo_source_sign string COMMENT '搜索来源标记',
        from_url_sign string COMMENT '来源页面标记',

        visit_count_id bigint COMMENT '访问人数id',
        visit_count_ip bigint COMMENT '访问ip',
        visit_count_session_id bigint COMMENT '访问session_id数',
        visit_count_msg_count bigint COMMENT '咨询人数',
        consult_probability double COMMENT '咨询概率'

)   ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' -- 以 \t结尾的行格式分隔字段
    STORED AS ORC TBLPROPERTIES ('orc.compress' = 'ZLIB'); --以orc格式存储  然后以ZLIB形式压缩



CREATE TEMPORARY TABLE dws_web_chat_ems_2019_17
(
    year                             string COMMENT '年',
    year_id                          string COMMENT '每年总访问用户量',
    year_ip                          string,
    year_session_id                  string,
    year_area                        string COMMENT '地区',
    year_area_id                     string,
    year_area_ip                     string,
    year_area_session_id             string,
    year_seo_source                  string,
    year_seo_source_id               string,
    year_seo_source_ip               string,
    year_seo_source_session_id       string,
    year_origin_channel              string,
    year_origin_channel_id           string,
    year_origin_channel_ip           string,
    year_origin_channel_session_id   string,
    year_msg_count                   string,
    year_msg_count_id                string,
    year_from_url                    string,
    year_from_url_id                 string,
    year_from_url_ip                 string,
    yera_from_url_session_id         string,
    quarter                          string,
    quarter_id                       string,
    quarter_ip                       string,
    quarter_session_id               string,
    quarter_area                     string,
    quarter_area_id                  string,
    quarter_area_ip                  string,
    quarter_area_session_id          string,
    quarter_seo_source               string,
    quarter_seo_source_id            string,
    quarter_seo_source_ip            string,
    quarter_seo_source_session_id    string,
    quarter_origin_channel           string,
    quarter_origin_channel_id        string,
    quarter_origin_channel_ip        string,
    quarter_origin_channel_seeion_id string,
    quarter_msg_count                string,
    quarter_msg_count_id             string,
    quarter_from_url                 string,
    querter_from_url_id              string,
    querter_from_url_ip              string,
    querter_from_url_session_id      string,
    month                            string,
    month_id                         string,
    month_ip                         string,
    month_session_id                 string,
    month_area                       string,
    month_area_id                    string,
    month_area_ip                    string,
    month_area_session_id            string,
    month_seo_source                 string,
    month_seo_source_id              string,
    month_seo_source_ip              string,
    month_seo_source_session_id      string,
    month_origin_channel             string,
    month_origin_channel_id          string,
    month_origin_channel_ip          string,
    month_origin_channel_seeion_id   string,
    month_msg_count                  string,
    month_msg_count_id               string,
    month_from_url                   string,
    month_from_url_id                string,
    month_from_url_ip                string,
    month_from_url_session_id        string,
    day                              string,
    day_id                           string,
    day_ip                           string,
    day_session_id                   string,
    day_area                         string,
    day_area_id                      string,
    day_area_ip                      string,
    day_area_session_id              string,
    day_seo_source                   string,
    day_seo_source_id                string,
    day_seo_source_ip                string,
    day_seo_source_session_id        string,
    day_origin_channel               string,
    day_origin_channel_id            string,
    day_origin_channel_ip            string,
    day_origin_channel_seeion_id     string,
    day_msg_count                    string,
    day_msg_count_id                 string,
    day_from_url                     string,
    day_from_url_id                  string,
    day_from_url_ip                  string,
    day_from_url_session_id          string
)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' -- 以 \t结尾的行格式分隔字段
    STORED AS ORC TBLPROPERTIES ('orc.compress' = 'ZLIB'); --以orc格式存储  然后以ZLIB形式压缩




"